"""
Copyright (c) 2022 Huawei Technologies Co.,Ltd.

openGauss is licensed under Mulan PSL v2.
You can use this software according to the terms and conditions of the Mulan PSL v2.
You may obtain a copy of Mulan PSL v2 at:

          http://license.coscl.org.cn/MulanPSL2

THIS SOFTWARE IS PROVIDED ON AN "AS IS" BASIS, WITHOUT WARRANTIES OF ANY KIND,
EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO NON-INFRINGEMENT,
MERCHANTABILITY OR FIT FOR A PARTICULAR PURPOSE.
See the Mulan PSL v2 for more details.
"""
"""
Case Type   : sqlines工具
Case Name   : sqlines工具-s参数指定oracle，查看支持的数据类型能否转换成功
Create At   : 2022/01/12
@zou_jialiang0501115624
Description :
    1、sqlines安装
    2、创建测试脚本
    3、sqlines -s=oracle -t=opengauss -in={self.in_file} -out={self.out_file}
     -log={self.log_file}
    4、环境清理：卸载sqlines、删除对应文件
Expect      :
    1、sqlines安装成功
    2、脚本创建成功
    3、转换成功
    4、环境清理成功
History     :
    modified: 2023-04-19 by @daiguatutu 优化unzip解压命令，避免命令执行超时失败
"""

import os
import unittest

from yat.test import Node
from yat.test import macro
from testcase.utils.Logger import Logger


class SqlinesTest(unittest.TestCase):

    def setUp(self):
        self.log = Logger()
        self.log.info(f'----- {os.path.basename(__file__)} start-----')
        self.root = Node('PrimaryRoot')
        self.in_file = f'{os.path.basename(__file__)[:-3]}.sql'
        self.out_file = f'{os.path.basename(__file__)[:-3]}_out.sql'
        self.log_file = f'{os.path.basename(__file__)[:-3]}.log'

    def test_main(self):
        step_txt = '----step1:安装sqlines，expect: sqlines安装成功----'
        self.log.info(step_txt)
        self.log.info('-------------------下载软件包--------------------')
        self.file_name = 'openGauss-tools-sqlines-master.zip'
        file_download = os.path.join(macro.FTP_PLUGINS_PATH,
                                     'sqlines', self.file_name)
        cmd = f'wget {file_download}'
        self.log.info(cmd)
        self.root.sh(cmd)
        cmd2 = f'ls {self.file_name}'
        self.log.info(cmd2)
        res = self.root.sh(cmd2).result()
        self.log.info(res)
        self.assertEqual(self.file_name, res, '文件下载失败：' + step_txt)

        self.log.info('-------------------解压软件包--------------------')
        cmd3 = f'timeout 30s unzip -o {self.file_name}'
        self.log.info(cmd3)
        res = self.root.sh(cmd3).result()
        self.log.info(res)
        self.unzip_dir = self.file_name.replace('.zip', '')
        cmd4 = f'ls -d {self.unzip_dir}'
        self.log.info(cmd4)
        res = self.root.sh(cmd4).result()
        self.log.info(res)
        self.assertEqual(self.unzip_dir, res, '文件解压失败：' + step_txt)

        self.log.info('-------------------安装sqlines--------------------')
        cmd = f'cd {self.unzip_dir};sh build.sh -i;pwd'
        self.log.info(cmd)
        res = self.root.sh(cmd).result()
        self.log.info(res)
        self.assertIn('Install Sqlines successfully', res,
                      'Sqlines安装失败：' + step_txt)
        tmp_path = res.strip().splitlines()[-1].strip()
        mid_path = os.path.join(tmp_path, 'bin')
        file_name = 'sqlines'
        check_file = os.path.join(mid_path, file_name)
        cmd = f"ls -l {check_file}"
        self.log.info(cmd)
        res = self.root.sh(cmd).result()
        self.log.info(res)
        self.assertEqual(res[3], 'x', '文件没有执行权限' + step_txt)
        self.log.info('-----------------配置sqlines环境变量-----------------')
        self.source_path = f"export PATH={mid_path}:$PATH"

        step_txt = '----step2:创建测试脚本，expect: 脚本创建成功----'
        self.log.info(step_txt)
        cmd1 = f"""cat >{self.in_file} <<EOF
        CREATE TABLE t_Opengauss_Function_Sqlines_Case0025( 
                          column1 BINARY_FLOAT,
                          column2 BINARY_DOUBLE,
                          column3 BLOB,
                          column4 CLOB,
                          column5 DATE,
                          column6 FLOAT,
                          column7 INTERVAL YEAR(4) TO MONTH,
                          column8 INTERVAL DAY(4) TO SECOND(8),
                          column81 TIMESTAMP WITH LOCAL TIME ZONE,
                          column9 LONG,
                          column10 LONG RAW,
                          column11 NCHAR(8),
                          column12 NCHAR VARYING(7),
                          column13 NCLOB,
                          column14 NUMBER(8),
                          column15 NUMBER(1,0),
                          column16 NUMBER(4,0),
                          column17 NUMBER(8,0),
                          column18 NUMBER(12,0),
                          column19 NUMBER(20, 0),
                          column20 NUMBER(10, 2),
                          column21 NUMBER,
                          column22 NUMBER(*),
                          column23 NVARCHAR2(12),
                          column24 RAW(8),
                          column25 REAL,
                          column26 SMALLINT,
                          column27 UROWID(16),
                          column28 VARCHAR2(18),
                          column29 BFILE,
                          column30 ROWID,
                          column32 XMLTYPE
                     );

        """
        self.log.info(cmd1)
        self.root.sh(cmd1)
        cmd2 = f'ls {self.in_file}'
        self.log.info(cmd2)
        res = self.root.sh(cmd2).result()
        self.log.info(res)
        self.assertEqual(res, f'{self.in_file}', '执行失败' + step_txt)

        step_txt = '----step3:执行sqlines命令，expect: 转换成功，' \
                   'oracle的数据类型转换为opengauss对应的数据类型----'
        self.log.info(step_txt)
        cmd = f"{self.source_path};" \
            f"sqlines -s=oracle -t=opengauss -in={self.in_file}" \
            f" -out={self.out_file} -log={self.log_file}"
        self.log.info(cmd)
        res = self.root.sh(cmd).result()
        self.log.info(res)
        self.assertNotIn('error', res, '执行失败：')
        cmd2 = f'cat {self.out_file}'
        self.log.info(cmd2)
        res = self.root.sh(cmd2).result()
        self.log.info(res)
        self.check_dict = {'': '',
                'BINARY_FLOAT': 'REAL',
                'BINARY_DOUBLE': 'DOUBLE PRECISION',
                'BLOB': 'BYTEA',
                'CLOB': 'TEXT',
                'DATE': 'TIMESTAMP',
                'FLOAT': 'DOUBLE PRECISION',
                'INTERVAL YEAR(4) TO MONTH': 'INTERVAL YEAR TO MONTH',
                'INTERVAL DAY(4) TO SECOND(8)': 'INTERVAL DAY TO SECOND(8)',
                'TIMESTAMP WITH LOCAL TIME ZONE': 'TIMESTAMP WITH TIME ZONE',
                'LONG': 'TEXT',
                'LONG RAW': 'BYTEA',
                'NCHAR(8)': 'CHAR(8)',
                'NCHAR VARYING(7)': 'VARCHAR(7)',
                'NCLOB': 'TEXT',
                'NUMBER(8)': 'INT',
                'NUMBER(1,0)': 'SMALLINT',
                'NUMBER(4,0)': 'SMALLINT',
                'NUMBER(8,0)': 'INT',
                'NUMBER(12,0)': 'BIGINT',
                'NUMBER(20,0)': 'DECIMAL(20, 0)',
                'NUMBER(10,2)': 'DECIMAL(10, 2)',
                'NUMBER': 'DOUBLE PRECISION',
                'NUMBER(*)': 'DOUBLE PRECISION',
                'NVARCHAR2(12)': 'VARCHAR(12)',
                'RAW(8)': 'BYTEA',
                'REAL': 'DOUBLE PRECISION',
                'SMALLINT': 'DECIMAL(38)',
                'UROWID(16)': 'VARCHAR(16)',
                'VARCHAR2(18)': 'VARCHAR(18)',
                'BFILE': 'VARCHAR(255)',
                'ROWID': 'CHAR(10)',
                'XMLTYPE': 'XML'}
        for i in range(1, 33):
            self.assertIn([j for j in self.check_dict.values()][i],
                          res.strip().splitlines()[i].strip(),
                f'数据类型{[j for j in self.check_dict.keys()][i]}转换失败'
                          + step_txt)

    def tearDown(self):
        step_txt = '----step4:环境清理，expect: 环境清理成功----'
        self.log.info(step_txt)
        self.log.info('--------------------卸载sqlines--------------------')
        cmd = f'cd {self.unzip_dir};sh build.sh -m'
        self.log.info(cmd)
        res = self.root.sh(cmd).result()
        self.log.info(res)
        self.log.info('--------------------删除相关文件--------------------')
        cmd = f"rm -rf {self.file_name} {self.unzip_dir} sqlines.log " \
            f"{self.in_file} {self.out_file} {self.log_file}"
        self.root.sh(cmd)
        self.assertIn('Uninstall Sqlines successfully', res,
                      'Sqlines卸载失败：' + step_txt)
        self.log.info(f'----- {os.path.basename(__file__)} end-----')
